package models;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToOne;
import javax.persistence.Table;

import com.avaje.ebean.Ebean;
import com.avaje.ebean.Expr;
import com.avaje.ebean.ExpressionList;
import com.avaje.ebean.SqlQuery;
import com.avaje.ebean.SqlRow;
import com.avaje.ebean.SqlUpdate;

import play.db.ebean.Model;

@Entity
@Table(name="doctor_schedule")
public class DoctorSchedule extends Model{

	public static final long serialVersionUID = 1L;

	@Id
	@Column(name="iddoctor_schedule")
	public long id;
	
	@Column(name="day")
    public Integer day;
    
    @Column(name="start_time")
    public String startTime;

    @Column(name="end_time")
    public String endTime;
    
	@OneToOne
	@JoinColumn(name="hospital_id")
	public DoctorHospital hospitals;
    
	@OneToOne
    @JoinColumn(name="iddoctor_details")
    public DoctorDetails doctorDetails;

     
    public static Model.Finder<Long,DoctorSchedule> find = new Model.Finder(Long.class, DoctorSchedule.class);

    public static List<DoctorSchedule> getDoctors(List<String> speciality, String doctorName, Integer day,Long hospital,String city){
    	List<DoctorSchedule> doctors = new ArrayList<DoctorSchedule>();
    	ExpressionList<DoctorSchedule>	doctorEx=	find
				.join("hospitals")
				.fetch("hospitals.hospitals")
				.fetch("doctorDetails")
				.fetch("doctorDetails.specialities")
				.fetch("doctorDetails.doctor")
				.where()
				.eq("approved", "Y");

		if(speciality !=null && speciality.size()>0){
			doctorEx.in("speciality", speciality);
		}
		StringBuilder s = new StringBuilder();
	   	if(hospital >0 ){
	   		//s.append("hospital_name ='"+hospital+"'");
	   		doctorEx.eq("idhospitals", hospital);
    	}
    	if(!"".equals(city)){
/*      		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " hospital_city = '"+city+"'");
*/    	
	   		doctorEx.eq("hospital_city", city);
    	}
/*    	if(s.length()>0){
    		List<Hospitals> hospitalList=Hospitals.find.where().add(Expr.raw(s.toString())).findList();
    		List<Long> l= new ArrayList<Long>();
    		for(Hospitals h:hospitalList){
    			l.add(h.id);
    		}
    		doctorEx.in("hospital_id", l);
    	}
*/		if(!"".equals(doctorName)){
			doctorEx.eq("name", doctorName);
		}
System.out.println("day:"+day);
		if(day >0){
			doctorEx.eq("day", day);
		}

		doctors= doctorEx.orderBy("name, day").findList();

    	return doctors;
    }
    
    public static List<DoctorSchedule> getDoctorScheduleDay(Long doctorId, Long day, Long hospitalId) {    
    	List<DoctorSchedule> doctorSchedule =find
													.join("hospitals")
													.fetch("hospitals.hospitals")
													.fetch("doctorDetails")
    												.where()
    												.eq("doctor_id",doctorId.intValue())
    												.eq("day", day.intValue())
    												.eq("idhospitals", hospitalId.intValue())
    												.findList();
    	return doctorSchedule;
    			
    }
    
     public static List<DoctorSchedule> getSchedule(long doctorId){
     	List<DoctorSchedule> doctorSchedule =find
				.join("hospitals")
				.fetch("hospitals.hospitals")
				.fetch("doctorDetails")
				.where()
				.eq("doctor_id",Long.valueOf(doctorId).intValue())
				.findList();
     			return doctorSchedule;


    	 }

     public static List<DoctorSchedule> getScheduleAtHospital(long doctorId, Long hospitalId){
      	List<DoctorSchedule> doctorSchedule =find
 				.join("hospitals")
 				.fetch("hospitals.hospitals")
 				.fetch("doctorDetails")
 				.where()
 				.eq("doctor_id",Long.valueOf(doctorId).intValue())
 				.eq("iddoctor_hospitals",hospitalId.intValue())
 				.findList();
      			return doctorSchedule;


     	 }

     public static List<DoctorSchedule> getSchedule4Mobile(long doctorId,long hospitalId){
      	List<DoctorSchedule> doctorSchedule =find
 				.join("hospitals")
 				.fetch("hospitals.hospitals")
 				.fetch("doctorDetails")
 				.where()
 				.eq("doctor_id",Long.valueOf(doctorId).intValue())
 				.eq("idhospitals",Long.valueOf(hospitalId).intValue())
 				.findList();
      			return doctorSchedule;


     	 }

     public static int addSchedule(DoctorSchedule doctorSchedule){
      	String sql = "insert into doctor_schedule (day, start_time, end_time, iddoctor_details,hospital_id) values (:day, :startTime, :endTime, :idDoctor,:idHospital)";
      	SqlUpdate sqlQuery = Ebean.createSqlUpdate(sql);
      	sqlQuery.setParameter("day", doctorSchedule.day);
      	sqlQuery.setParameter("startTime", doctorSchedule.startTime);
      	sqlQuery.setParameter("endTime", doctorSchedule.endTime);
      	sqlQuery.setParameter("idDoctor", doctorSchedule.doctorDetails.id);
      	sqlQuery.setParameter("idHospital", doctorSchedule.hospitals.id);

      	int state= sqlQuery.execute();
      	return state;
      	
   	 }

     public static int deleteSchedule(long idDoctor, long hospitalId){
       	String sql = "delete from doctor_schedule where iddoctor_details=:idDoctor and hospital_id=:hospitalId";
       	SqlUpdate sqlQuery = Ebean.createSqlUpdate(sql);
       	sqlQuery.setParameter("idDoctor", idDoctor);
       	sqlQuery.setParameter("hospitalId", hospitalId);
       	int state= sqlQuery.execute();
       	return state;
   	 }

     public static int deleteSchedules(long idDoctor, long hospitalId ,List<Long> schedules){
        	String sql = "delete from doctor_schedule where iddoctor_details=:idDoctor and hospital_id=:hospitalId and iddoctor_schedule in (:scheduleIds)";
        	SqlUpdate sqlQuery = Ebean.createSqlUpdate(sql);
           	sqlQuery.setParameter("idDoctor", idDoctor);
           	sqlQuery.setParameter("hospitalId", hospitalId);
        	sqlQuery.setParameter("scheduleIds", schedules);
        	int state= sqlQuery.execute();
        	return state;
    	 }
}
